<?php
/**
 * MySQL处理类 Mysql.class.php
 * Author: 勾国印 (phper@gouguoyin.cn)
 * Date: 2015-5-14 下午2:40:57
*/
namespace Common\Lib;
class Mysql{

    /*
     * 创建数据库，并且主键是id
     * table 要查询的表名
     */
    static public  function createTable($table){
        $table = C('DB_PREFIX').$table;
        $sql="CREATE TABLE IF NOT EXISTS `$table` (`id` INT(10) NOT NULL AUTO_INCREMENT primary key,`title` VARCHAR(255) NOT NULL,`cover` int(10) NOT NULL DEFAULT '0',`intro` VARCHAR(255) NULL,`content` MEDIUMTEXT NOT NULL,`category_id` INT( 10 ) NOT NULL COMMENT  '所属栏目id',`positions` VARCHAR( 255 ) NOT NULL COMMENT  '推荐位',`tags` VARCHAR( 255 ) NOT NULL COMMENT  'tag标签',`hits` INT( 10 ) NOT NULL COMMENT  '点击数',`max` INT( 10 ) NOT NULL NULL DEFAULT '255' COMMENT  '最大值/宽度',`min` INT( 10 ) NOT NULL DEFAULT '0' COMMENT  '最小值/高度',`status` TINYINT(1) NOT NULL DEFAULT '1' COMMENT  '状态,1：正常，0：回收站，默认1',`add_time` DATETIME NOT NULL,`update_time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT  '更新时间')ENGINE = InnoDB  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
        M()->execute($sql);
    }
	
	/*
     * 检测表是否存在，也可以获取表中所有字段的信息
     * table 要查询的表名
     * return bool
     */
    static public function checkTable($table){
    	
        $info=M($table)->getDbError();
		if($info){
			return false;
		}else{
			return true;
		}
    }

    /*
     * 删除表
    * table 要删除的表名
    */
    static public function dropTable($table){
        $table = C('DB_PREFIX').$table;
        $sql="DROP TABLE IF EXISTS $table;";
        M()->execute($sql);
    }

    /*
     * 添加字段
     * table 表名
     * info  字段信息数组 array
     * return 字段信息 array
     */
    static public function addField($table,$info){
        $table = C('DB_PREFIX').$table;
        $sql="ALTER TABLE `$table` ADD ";
        $sql.=self::filterFieldInfo($info);
        $result = M()->execute($sql);
    }
    
    /*
     * 检测字段是否存在，也可以获取字段信息(只能是一个字段)
     * table 表名
     * field 字段名
     */
    static public function checkField($table,$field){
        $sql='desc `$table` $field';
        $info=M()->execute($sql);
        return $info;
    }
	
    /*
     * 修改字段
     * 不能修改字段名称，只能修改
     */
    static public function editField($table,$info){
        $table = C('DB_PREFIX').$table;
        $sql="alter table `$table` modify ";
        $sql.=$this->filterFieldInfo($info);
        M()->execute($sql);
        $this->checkField($table,$info['name']);
    }
    /*
     * 字段信息数组处理，供添加更新字段时候使用
     * info[name]   字段名称
     * info[type]   字段类型
     * info[length]  字段长度
     * info[isNull]  是否为空
     * info['default']   字段默认值
     * info['comment']   字段备注
     */
    private function filterFieldInfo($info){
        if(!is_array($info)){
            return;
        }
        $name    = $info['name'];
        $type    = $info['type'];
        $length  = $info['length'];
        $isNull  = $info['isNull'];
        $default = $info['default'];
        $comment = $info['comment'];

        $newInfo=array();
        $newInfo['name'] = "`$name`";
        $newInfo['type'] = $type;
        if($type == 'varchar'){
            $newInfo['length']=empty($length)?'(255)':'('.$length.')';
        }else if($newInfo['type'] == 'int'){
            $newInfo['length']=empty($length)?'(10)':'('.$length.')';
        }else if($newInfo['type'] == 'tinyint'){
            $newInfo['length']=empty($length)?'(1)':'('.$length.')';
        }

        $newInfo['isNull']=$isNull==1?'NULL':'NOT NULL';
        $newInfo['default']=empty($default)?'':'DEFAULT '.$default;
        $newInfo['comment']=empty($comment)?'':'COMMENT '."'".$comment."'";

        $sql=$newInfo['name']." ".$newInfo['type'];
        $sql.=(!empty($newInfo['length']))?($newInfo['length']) .' ':' ';
        $sql.=$newInfo['isNull'].' ';
        $sql.=$newInfo['default'];
        $sql.=$newInfo['comment'];
        return $sql;
    }
    /*
     * 删除字段
     * 如果返回了字段信息则说明删除失败，返回false，则为删除成功
     */
    static public function dropField($table,$field){
        $table = C('DB_PREFIX').$table;
        $sql="ALTER TABLE `$table` DROP `$field`";
        M()->execute($sql);
    }
    /*
     * 获取指定表中指定字段的信息(多字段)
     */
    static public function getFieldInfo($table,$field){
        $table = C('DB_PREFIX').$table;
        $info=array();
        if(is_string($field)){
            $this->checkField($table,$field);
        }else{
            foreach($field as $v){
                $info[$v]=$this->checkField($table,$v);
            }
        }
        return $info;
    }
 }